﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Text;
using Framework;

namespace Enterprise
{
    /// <summary>
    /// 应聘信息管理
    /// </summary>
    public class RecruitmentService : BaseService
    {
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string post, int pageValue, int rowsValue)
        {
            var parameters = new[]
                             {
                               new SQLiteParameter("@CoPost", DbType.String, 255),
                               new SQLiteParameter("@startIndex", DbType.Int32),
                               new SQLiteParameter("@endIndex", DbType.Int32)
                             };
            parameters[0].Value = post.SqliteLike();
            parameters[1].Value = pageValue > 0 ? (pageValue - 1) * rowsValue : 0;
            parameters[2].Value = rowsValue;
            var strWhere = new StringBuilder();
            strWhere.Append(" where  CoPost like '%'||@CoPost||'%' escape '\\'  ");
            var sqlOne = new StringBuilder();
            sqlOne.Append("select count(*) from TbRecruitment ");
            sqlOne.Append(strWhere);
            int total = DbHelperSqLite.GetSingle(sqlOne.ToString(), parameters).SafeToInt();
            var sqlTwo = new StringBuilder();
            sqlTwo.Append("select * from TbRecruitment ");
            sqlTwo.Append(strWhere);
            sqlTwo.Append(" order by corecruitmentid desc ");
            sqlTwo.Append(" LIMIT @endIndex OFFSET @startIndex ");
            var ds = DbHelperSqLite.Query(sqlTwo.ToString(), parameters);
            var table = new Table
                 {
                     Rows = ds.Tables["ds"].Rows,
                     Total = total
                 };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            var parameters = new[] {
                                     new SQLiteParameter("@id", DbType.Int32)
                                   };
            parameters[0].Value = id;
            return DbHelperSqLite.Query("select *  from TbRecruitment  where CoRecruitmentId=@id", parameters).Tables["ds"].Rows[0];
        }
        public int Add(string post, string postDescription, string number, string monthlySalary,
                       string workPlace, string workExperience, string education, string age, DateTime date,
                       DateTime validDate, string language, string assess, string candidates, string email,
                       string contact, string otherTreatment, string qq, string workType, string sex)
        {
            Check.Argument.IsNotOutOfLength(post, 25, true, "应聘岗位");
            Check.Argument.IsNotInvalidEmail(email, true, "邮箱");
            Check.Argument.IsNotInvalidSqlDateTime(validDate, "有效期");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into TbRecruitment(");
            strSqlOne.Append("CoPost,CoPostDescription,CoNumber,CoMonthlySalary,CoWorkPlace,CoWorkExperience,CoEducation,CoAge,CoDate,CoValidDate,CoLanguage,CoAssess,CoCandidates,CoEmail,CoContact,CoOtherTreatment,CoQq,CoWorkType,CoSex");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoPost,@CoPostDescription,@CoNumber,@CoMonthlySalary,@CoWorkPlace,@CoWorkExperience,@CoEducation,@CoAge,@CoDate,@CoValidDate,@CoLanguage,@CoAssess,@CoCandidates,@CoEmail,@CoContact,@CoOtherTreatment,@CoQq,@CoWorkType,@CoSex");
            strSqlOne.Append(");");
            strSqlOne.Append("select last_insert_rowid() from TbRecruitment;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoPost", DbType.String),
                    new SQLiteParameter("@CoPostDescription", DbType.String),
                    new SQLiteParameter("@CoNumber", DbType.String, 255),
                    new SQLiteParameter("@CoMonthlySalary", DbType.String, 255),  
                    new SQLiteParameter("@CoWorkPlace", DbType.String, 255),
                    new SQLiteParameter("@CoWorkExperience", DbType.String, 255),
                    new SQLiteParameter("@CoEducation", DbType.String, 255),
                    new SQLiteParameter("@CoAge", DbType.String, 255),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoValidDate", DbType.DateTime),
                    new SQLiteParameter("@CoLanguage", DbType.String, 255),
                    new SQLiteParameter("@CoAssess", DbType.String, 255),
                    new SQLiteParameter("@CoCandidates", DbType.String, 255),
                    new SQLiteParameter("@CoEmail", DbType.String, 255),
                    new SQLiteParameter("@CoContact", DbType.String, 255),
                    new SQLiteParameter("@CoOtherTreatment", DbType.String),
                    new SQLiteParameter("@CoQq", DbType.String, 255),
                    new SQLiteParameter("@CoWorkType", DbType.String, 255),
                    new SQLiteParameter("@CoSex", DbType.String, 5)
                };
            parametersOne[0].Value = post;
            parametersOne[1].Value = postDescription;
            parametersOne[2].Value = number;
            parametersOne[3].Value = monthlySalary;
            parametersOne[4].Value = workPlace;
            parametersOne[5].Value = workExperience;
            parametersOne[6].Value = education;
            parametersOne[7].Value = age;
            parametersOne[8].Value = date;
            parametersOne[9].Value = validDate;
            parametersOne[10].Value = language;
            parametersOne[11].Value = assess;
            parametersOne[12].Value = candidates;
            parametersOne[13].Value = email;
            parametersOne[14].Value = contact;
            parametersOne[15].Value = otherTreatment;
            parametersOne[16].Value = qq;
            parametersOne[17].Value = workType;
            parametersOne[18].Value = sex;
            var obj = DbHelperSqLite.GetSingle(strSqlOne.ToString(), parametersOne).SafeToInt32();
            return obj;
        }
        public int Edit(int id, string post, string postDescription, string number, string monthlySalary,
                       string workPlace, string workExperience, string education, string age, DateTime date,
                       DateTime validDate, string language, string assess, string candidates, string email,
                       string contact, string otherTreatment, string qq, string workType, string sex)
        {
            Check.Argument.IsNotOutOfLength(post, 25, true, "应聘岗位");
            Check.Argument.IsNotInvalidEmail(email, true, "邮箱");
            Check.Argument.IsNotInvalidSqlDateTime(validDate, "有效期");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("update TbRecruitment set ");
            strSqlOne.Append("CoPost=@CoPost");
            strSqlOne.Append(",");
            strSqlOne.Append("CoPostDescription=@CoPostDescription");
            strSqlOne.Append(",");
            strSqlOne.Append("CoNumber=@CoNumber");
            strSqlOne.Append(",");
            strSqlOne.Append("CoMonthlySalary=@CoMonthlySalary");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkPlace=@CoWorkPlace");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkExperience=@CoWorkExperience");
            strSqlOne.Append(",");
            strSqlOne.Append("CoEducation=@CoEducation");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAge=@CoAge");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDate=@CoDate");
            strSqlOne.Append(",");
            strSqlOne.Append("CoValidDate=@CoValidDate");
            strSqlOne.Append(",");
            strSqlOne.Append("CoLanguage=@CoLanguage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAssess=@CoAssess");
            strSqlOne.Append(",");
            strSqlOne.Append("CoCandidates=@CoCandidates");
            strSqlOne.Append(",");
            strSqlOne.Append("CoEmail=@CoEmail");
            strSqlOne.Append(",");
            strSqlOne.Append("CoContact=@CoContact");
            strSqlOne.Append(",");
            strSqlOne.Append("CoOtherTreatment=@CoOtherTreatment");
            strSqlOne.Append(",");
            strSqlOne.Append("CoQq=@CoQq");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkType=@CoWorkType");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSex=@CoSex");
            strSqlOne.Append(" where CoRecruitmentId=@id;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoPost", DbType.String),
                    new SQLiteParameter("@CoPostDescription", DbType.String),
                    new SQLiteParameter("@CoNumber", DbType.String, 255),
                    new SQLiteParameter("@CoMonthlySalary", DbType.String, 255),  
                    new SQLiteParameter("@CoWorkPlace", DbType.String, 255),
                    new SQLiteParameter("@CoWorkExperience", DbType.String, 255),
                    new SQLiteParameter("@CoEducation", DbType.String, 255),
                    new SQLiteParameter("@CoAge", DbType.String, 255),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoValidDate", DbType.DateTime),
                    new SQLiteParameter("@CoLanguage", DbType.String, 255),
                    new SQLiteParameter("@CoAssess", DbType.String, 255),
                    new SQLiteParameter("@CoCandidates", DbType.String, 255),
                    new SQLiteParameter("@CoEmail", DbType.String, 255),
                    new SQLiteParameter("@CoContact", DbType.String, 255),
                    new SQLiteParameter("@CoOtherTreatment", DbType.String),
                    new SQLiteParameter("@CoQq", DbType.String, 255),
                    new SQLiteParameter("@CoWorkType", DbType.String, 255),
                    new SQLiteParameter("@id", DbType.Int32),
                    new SQLiteParameter("@CoSex", DbType.String, 5)
                };
            parametersOne[0].Value = post;
            parametersOne[1].Value = postDescription;
            parametersOne[2].Value = number;
            parametersOne[3].Value = monthlySalary;
            parametersOne[4].Value = workPlace;
            parametersOne[5].Value = workExperience;
            parametersOne[6].Value = education;
            parametersOne[7].Value = age;
            parametersOne[8].Value = date;
            parametersOne[9].Value = validDate;
            parametersOne[10].Value = language;
            parametersOne[11].Value = assess;
            parametersOne[12].Value = candidates;
            parametersOne[13].Value = email;
            parametersOne[14].Value = contact;
            parametersOne[15].Value = otherTreatment;
            parametersOne[16].Value = qq;
            parametersOne[17].Value = workType;
            parametersOne[18].Value = id;
            parametersOne[19].Value = sex;
            var obj = DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parametersOne).SafeToInt();
            return obj;
        }
        public void Delete(int id)
        {
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("delete  from TbRecruitment  where CoRecruitmentId=@id;");
            var parameterOne = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parameterOne[0].Value = id;
            DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parameterOne);
        }
    }
}
